package com.example.canteen.dao;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Optional;
import java.util.Set;

public interface SetMealRepository extends JpaRepository<SetMeal, Long> {
    SetMeal findByName(String name);

    // 使用原生SQL更新中间表quantity
    @Transactional(isolation = Isolation.READ_COMMITTED)
    @Modifying
    @Query(value = "UPDATE set_meal_dish SET quantity = :quantity WHERE set_meal_id = :setMealId AND dish_id = :dishId", nativeQuery = true)
    void updateSetMealDishQuantity(long setMealId, long dishId, int quantity);
    @Transactional(isolation = Isolation.READ_COMMITTED)
    @Modifying
    @Query(value = "INSERT INTO set_meal_dish (set_meal_id, dish_id, quantity) VALUES (:setMealId, :dishId, :quantity)", nativeQuery = true)
    void insertSetMealDish(long setMealId, long dishId, int quantity);

    // 使用原生SQL查询中间表quantity
    @Query(value = "SELECT quantity FROM set_meal_dish WHERE set_meal_id = :setMealId AND dish_id = :dishId", nativeQuery = true)
    Integer findQuantityBySetMealIdAndDishId(long setMealId, long dishId);

    @Query(value = "SELECT dish_id FROM set_meal_dish WHERE set_meal_id = :setMealId", nativeQuery = true)
    Set<Long> findDishIdBySetMealId(long setMealId);

    // 删除指定套餐与指定菜品的关联关系
    @Transactional(isolation = Isolation.READ_COMMITTED)
    @Modifying
    @Query(value = "DELETE FROM set_meal_dish WHERE set_meal_id = :setMealId AND dish_id = :dishId", nativeQuery = true)
    void deleteSetMealDish(long setMealId, long dishId);
}